/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.digwener.db;

/**
 *
 * @author ADBerezin
 */

import java.sql.*;
import java.util.*;
import com.digwener.entities.users.*;


public class RespTable {
    DBConnection dbConnection = DBConnection.getInstance();
    Connection sqlConnection = null;
    ObjectFactory objectFactory = new ObjectFactory();
    
    UserType userType;
    
    String userName;

    public void setUserName(String userName) {
        this.userName = userName;
    }
    
    public RespTable(){
        userType = objectFactory.createUserType();
    }
    public UserType getUserType() {
        return userType;
    }

    public void setUserType(UserType userType) {
        this.userType = userType;
    }
    
    
    
    public void loadFromDB(){
        if (!dbConnection.isConnected()) 
            return;
        
        sqlConnection = dbConnection.getSQLConnection();
        
        userType.getResponsibilities().clear();
        
        try {
            String query = "select rtl.responsibility_name from fnd_user_resp_groups_all frg, fnd_user fu, fnd_responsibility_tl rtl where fu.user_name = ? "
                           +" and fu.user_id = frg.user_id "
+" and frg.responsibility_id = rtl.responsibility_id and rtl.language = 'RU' order by rtl.responsibility_name";

                            
            //System.out.println("query launched");
            PreparedStatement cs = sqlConnection.prepareCall(query);
            cs.setString(1,userName);
            ResultSet rs = cs.executeQuery();
            String respName;
            while(rs.next()){
                 respName = rs.getString(1);
                 System.out.println(respName);
                 ResponsibilityType respType = objectFactory.createResponsibilityType();
                 respType.setRespName(respName);
                 userType.getResponsibilities().add(respType);
            }
         
        } catch (SQLException e) {
            System.out.println(e.toString());
            return;
        }
        
    }
    
    private boolean doesUserExist(Connection sqlConnection,String userName){
         try {
            String query = "select case when exists (select 1 from fnd_user where user_name =  ? ) then 1 else 0 end result from dual;";

                            
            //System.out.println("query launched");
            PreparedStatement cs = sqlConnection.prepareCall(query);
            cs.setString(1,userName);
            ResultSet rs = cs.executeQuery();
            rs.next();
            int res = rs.getInt(1);
            return res==1;
         
        } catch (SQLException e) {
            System.out.println(e.toString());
            return false;
        }
    }
    
    
    
    public void saveToDB(){
         if (!dbConnection.isConnected()) 
            return;
        
        sqlConnection = dbConnection.getSQLConnection();
        //userName = "ADBEREZIN";
        String password = "q1w2e3r4";
        String firstPassword = password + "q1";
        
        String addUser = "";
        boolean creation = !doesUserExist(sqlConnection,userName);
        if(creation){
            addUser = "  fnd_user_pkg.createuser(x_user_name => p_user_name , \n"+
                         " x_owner => 'CUST', \n"+
                          " x_unencrypted_password => p_first_password, \n"+
                          " x_description => 'Березин Антон Дмитриевич' \n); \n"
                    + "p := fnd_user_pkg.ChangePassword(p_user_name,p_password); \n";
                          
        }
        
        
        String respList = "";
        for(ResponsibilityType resp:userType.getResponsibilities()){
            
            respList += "'"+ resp.getRespName()+"'"+",";
        }
        respList = respList.substring(0,respList.length()-1);
        
        try {
            String query = " declare \n"
                    + " p boolean; \n"
                    + " p_user_name varchar2(100) := ? ; \n"
                    + " p_first_password varchar2(100) := ? ; \n "
                    + "p_password varchar2(100) := ? ; \n"
                    + " begin \n" + addUser 
                    + "FOR cc_resp IN  \n"+
               "(SELECT f.responsibility_key \n"+
        " , a.application_short_name \n" +
        " FROM fnd_responsibility    f \n" +
        " , fnd_responsibility_tl l \n" +
        " , fnd_application       a \n" +
        " WHERE f.application_id = l.application_id \n"+
        " AND f.responsibility_id = l.responsibility_id \n"+
        " AND a.application_id = l.application_id \n" +
        " AND l.responsibility_name IN ( " + respList + " ) ) \n" +
        " LOOP \n" +
        " fnd_user_pkg.addresp( p_user_name , " +
                         " cc_resp.application_short_name, cc_resp.responsibility_key, " +
                        "  'STANDARD', 'DESCRIPTION', sysdate, null); \n" +
        " END LOOP; \n" + " commit; \n end;\n ";
            
            System.out.println(query);
            PreparedStatement cs = sqlConnection.prepareCall(query);
            cs.setString(1,userName);
            cs.setString(2,firstPassword);
            cs.setString(3,password);
            cs.executeQuery();
            
         
        } catch (SQLException e) {
            System.out.println(e.toString());
            return;
        }
        
    }
    
    
}
